﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;

namespace TimKen.DataAccess
{
    /// <summary>
    /// 数据访问类Customer。
    /// </summary>
    public class CustomerDAO
    {
        public CustomerDAO()
        { }
        #region  成员方法
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int CustomerID)
        {
            int rowsAffected;
            SqlParameter[] parameters = {
					new SqlParameter("@CustomerID", SqlDbType.Int,4)};
            parameters[0].Value = CustomerID;

            int result = DbHelperSQL.RunProcedure("UP_Customer_Exists", parameters, out rowsAffected);
            if (result == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        ///  增加一条数据
        /// </summary>
        public int Add(TimKen.DataEntity.Customer model)
        {
            int rowsAffected;
            SqlParameter[] parameters = {
					new SqlParameter("@CustomerID", SqlDbType.Int,4),
					new SqlParameter("@CardCode", SqlDbType.NVarChar,15),
					new SqlParameter("@CustomerType", SqlDbType.Int,4),
					new SqlParameter("@CustomerForeignName", SqlDbType.NVarChar,100),
					new SqlParameter("@CustomerName", SqlDbType.NVarChar,100),
					new SqlParameter("@ContactPerson", SqlDbType.NVarChar,90),
					new SqlParameter("@Telephone", SqlDbType.NVarChar,20),
					new SqlParameter("@Mobile", SqlDbType.NVarChar,50),
					new SqlParameter("@Fax", SqlDbType.NVarChar,20),
					new SqlParameter("@Address", SqlDbType.NVarChar,100),
					new SqlParameter("@Country", SqlDbType.NVarChar,3),
					new SqlParameter("@Province", SqlDbType.NVarChar,100),
					new SqlParameter("@City", SqlDbType.NVarChar,100),
					new SqlParameter("@County", SqlDbType.NVarChar,100),
					new SqlParameter("@CreditLimit", SqlDbType.Float,8),
					new SqlParameter("@Currency", SqlDbType.NChar,5),
					new SqlParameter("@CurrentAccountBalance", SqlDbType.Float,8),
					new SqlParameter("@OpenDeliveryNotesBalance", SqlDbType.Float,8),
					new SqlParameter("@OpenOrdersBalance", SqlDbType.Float,8),
					new SqlParameter("@ECOrdersBalance", SqlDbType.Float,8),
					new SqlParameter("@DiscountPercent", SqlDbType.Float,8),
					new SqlParameter("@TaxRate", SqlDbType.Float,8),
					new SqlParameter("@TaxClassID", SqlDbType.Int,4),
					new SqlParameter("@Email", SqlDbType.NVarChar,100),
					new SqlParameter("@LanguageID", SqlDbType.Int,4),
					new SqlParameter("@SalesPersonCode", SqlDbType.Int,4),
					new SqlParameter("@ShippingType", SqlDbType.NVarChar,20),
					new SqlParameter("@ShipToDefault", SqlDbType.NVarChar,100),
					new SqlParameter("@Frozen", SqlDbType.Bit,1),
					new SqlParameter("@FrozenFrom", SqlDbType.DateTime),
					new SqlParameter("@FrozenRemarks", SqlDbType.NVarChar,30),
					new SqlParameter("@FrozenTo", SqlDbType.DateTime),
					new SqlParameter("@Valid", SqlDbType.Bit,1),
					new SqlParameter("@ValidFrom", SqlDbType.DateTime),
					new SqlParameter("@ValidRemarks", SqlDbType.NVarChar,30),
					new SqlParameter("@ValidTo", SqlDbType.DateTime),
					new SqlParameter("@Notes", SqlDbType.NVarChar,100),
					new SqlParameter("@Description", SqlDbType.Text)};
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = model.CardCode;
            parameters[2].Value = model.CustomerType;
            parameters[3].Value = model.CustomerForeignName;
            parameters[4].Value = model.CustomerName;
            parameters[5].Value = model.ContactPerson;
            parameters[6].Value = model.Telephone;
            parameters[7].Value = model.Mobile;
            parameters[8].Value = model.Fax;
            parameters[9].Value = model.Address;
            parameters[10].Value = model.Country;
            parameters[11].Value = model.Province;
            parameters[12].Value = model.City;
            parameters[13].Value = model.County;
            parameters[14].Value = model.CreditLimit;
            parameters[15].Value = model.Currency;
            parameters[16].Value = model.CurrentAccountBalance;
            parameters[17].Value = model.OpenDeliveryNotesBalance;
            parameters[18].Value = model.OpenOrdersBalance;
            parameters[19].Value = model.ECOrdersBalance;
            parameters[20].Value = model.DiscountPercent;
            parameters[21].Value = model.TaxRate;
            parameters[22].Value = model.TaxClassID;
            parameters[23].Value = model.Email;
            parameters[24].Value = model.LanguageID;
            parameters[25].Value = model.SalesPersonCode;
            parameters[26].Value = model.ShippingType;
            parameters[27].Value = model.ShipToDefault;
            parameters[28].Value = model.Frozen;
            parameters[29].Value = model.FrozenFrom;
            parameters[30].Value = model.FrozenRemarks;
            parameters[31].Value = model.FrozenTo;
            parameters[32].Value = model.Valid;
            parameters[33].Value = model.ValidFrom;
            parameters[34].Value = model.ValidRemarks;
            parameters[35].Value = model.ValidTo;
            parameters[36].Value = model.Notes;
            parameters[37].Value = model.Description;

            DbHelperSQL.RunProcedure("UP_Customer_ADD", parameters, out rowsAffected);
            return (int)parameters[0].Value;
        }

        /// <summary>
        ///  更新一条数据
        /// </summary>
        public void Update(TimKen.DataEntity.Customer model)
        {
            int rowsAffected;
            SqlParameter[] parameters = {
					new SqlParameter("@CustomerID", SqlDbType.Int,4),
					new SqlParameter("@CardCode", SqlDbType.NVarChar,15),
					new SqlParameter("@CustomerType", SqlDbType.Int,4),
					new SqlParameter("@CustomerForeignName", SqlDbType.NVarChar,100),
					new SqlParameter("@CustomerName", SqlDbType.NVarChar,100),
					new SqlParameter("@ContactPerson", SqlDbType.NVarChar,90),
					new SqlParameter("@Telephone", SqlDbType.NVarChar,20),
					new SqlParameter("@Mobile", SqlDbType.NVarChar,50),
					new SqlParameter("@Fax", SqlDbType.NVarChar,20),
					new SqlParameter("@Address", SqlDbType.NVarChar,100),
					new SqlParameter("@Country", SqlDbType.NVarChar,3),
					new SqlParameter("@Province", SqlDbType.NVarChar,100),
					new SqlParameter("@City", SqlDbType.NVarChar,100),
					new SqlParameter("@County", SqlDbType.NVarChar,100),
					new SqlParameter("@CreditLimit", SqlDbType.Float,8),
					new SqlParameter("@Currency", SqlDbType.NChar,5),
					new SqlParameter("@CurrentAccountBalance", SqlDbType.Float,8),
					new SqlParameter("@OpenDeliveryNotesBalance", SqlDbType.Float,8),
					new SqlParameter("@OpenOrdersBalance", SqlDbType.Float,8),
					new SqlParameter("@ECOrdersBalance", SqlDbType.Float,8),
					new SqlParameter("@DiscountPercent", SqlDbType.Float,8),
					new SqlParameter("@TaxRate", SqlDbType.Float,8),
					new SqlParameter("@TaxClassID", SqlDbType.Int,4),
					new SqlParameter("@Email", SqlDbType.NVarChar,100),
					new SqlParameter("@LanguageID", SqlDbType.Int,4),
					new SqlParameter("@SalesPersonCode", SqlDbType.Int,4),
					new SqlParameter("@ShippingType", SqlDbType.NVarChar,20),
					new SqlParameter("@ShipToDefault", SqlDbType.NVarChar,100),
					new SqlParameter("@Frozen", SqlDbType.Bit,1),
					new SqlParameter("@FrozenFrom", SqlDbType.DateTime),
					new SqlParameter("@FrozenRemarks", SqlDbType.NVarChar,30),
					new SqlParameter("@FrozenTo", SqlDbType.DateTime),
					new SqlParameter("@Valid", SqlDbType.Bit,1),
					new SqlParameter("@ValidFrom", SqlDbType.DateTime),
					new SqlParameter("@ValidRemarks", SqlDbType.NVarChar,30),
					new SqlParameter("@ValidTo", SqlDbType.DateTime),
					new SqlParameter("@Notes", SqlDbType.NVarChar,100),
					new SqlParameter("@Description", SqlDbType.Text)};
            parameters[0].Value = model.CustomerID;
            parameters[1].Value = model.CardCode;
            parameters[2].Value = model.CustomerType;
            parameters[3].Value = model.CustomerForeignName;
            parameters[4].Value = model.CustomerName;
            parameters[5].Value = model.ContactPerson;
            parameters[6].Value = model.Telephone;
            parameters[7].Value = model.Mobile;
            parameters[8].Value = model.Fax;
            parameters[9].Value = model.Address;
            parameters[10].Value = model.Country;
            parameters[11].Value = model.Province;
            parameters[12].Value = model.City;
            parameters[13].Value = model.County;
            parameters[14].Value = model.CreditLimit;
            parameters[15].Value = model.Currency;
            parameters[16].Value = model.CurrentAccountBalance;
            parameters[17].Value = model.OpenDeliveryNotesBalance;
            parameters[18].Value = model.OpenOrdersBalance;
            parameters[19].Value = model.ECOrdersBalance;
            parameters[20].Value = model.DiscountPercent;
            parameters[21].Value = model.TaxRate;
            parameters[22].Value = model.TaxClassID;
            parameters[23].Value = model.Email;
            parameters[24].Value = model.LanguageID;
            parameters[25].Value = model.SalesPersonCode;
            parameters[26].Value = model.ShippingType;
            parameters[27].Value = model.ShipToDefault;
            parameters[28].Value = model.Frozen;
            parameters[29].Value = model.FrozenFrom;
            parameters[30].Value = model.FrozenRemarks;
            parameters[31].Value = model.FrozenTo;
            parameters[32].Value = model.Valid;
            parameters[33].Value = model.ValidFrom;
            parameters[34].Value = model.ValidRemarks;
            parameters[35].Value = model.ValidTo;
            parameters[36].Value = model.Notes;
            parameters[37].Value = model.Description;

            DbHelperSQL.RunProcedure("UP_Customer_Update", parameters, out rowsAffected);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(int CustomerID)
        {
            int rowsAffected;
            SqlParameter[] parameters = {
					new SqlParameter("@CustomerID", SqlDbType.Int,4)};
            parameters[0].Value = CustomerID;

            DbHelperSQL.RunProcedure("UP_Customer_Delete", parameters, out rowsAffected);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public TimKen.DataEntity.Customer GetModel(int CustomerID)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@CustomerID", SqlDbType.Int,4)};
            parameters[0].Value = CustomerID;

            TimKen.DataEntity.Customer model = new TimKen.DataEntity.Customer();
            DataSet ds = DbHelperSQL.RunProcedure("UP_Customer_GetModel", parameters, "ds");
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["CustomerID"].ToString() != "")
                {
                    model.CustomerID = int.Parse(ds.Tables[0].Rows[0]["CustomerID"].ToString());
                }
                model.CardCode = ds.Tables[0].Rows[0]["CardCode"].ToString();
                if (ds.Tables[0].Rows[0]["CustomerType"].ToString() != "")
                {
                    model.CustomerType = int.Parse(ds.Tables[0].Rows[0]["CustomerType"].ToString());
                }
                model.CustomerForeignName = ds.Tables[0].Rows[0]["CustomerForeignName"].ToString();
                model.CustomerName = ds.Tables[0].Rows[0]["CustomerName"].ToString();
                model.ContactPerson = ds.Tables[0].Rows[0]["ContactPerson"].ToString();
                model.Telephone = ds.Tables[0].Rows[0]["Telephone"].ToString();
                model.Mobile = ds.Tables[0].Rows[0]["Mobile"].ToString();
                model.Fax = ds.Tables[0].Rows[0]["Fax"].ToString();
                model.Address = ds.Tables[0].Rows[0]["Address"].ToString();
                model.Country = ds.Tables[0].Rows[0]["Country"].ToString();
                model.Province = ds.Tables[0].Rows[0]["Province"].ToString();
                model.City = ds.Tables[0].Rows[0]["City"].ToString();
                model.County = ds.Tables[0].Rows[0]["County"].ToString();
                if (ds.Tables[0].Rows[0]["CreditLimit"].ToString() != "")
                {
                    model.CreditLimit = decimal.Parse(ds.Tables[0].Rows[0]["CreditLimit"].ToString());
                }
                model.Currency = ds.Tables[0].Rows[0]["Currency"].ToString();
                if (ds.Tables[0].Rows[0]["CurrentAccountBalance"].ToString() != "")
                {
                    model.CurrentAccountBalance = decimal.Parse(ds.Tables[0].Rows[0]["CurrentAccountBalance"].ToString());
                }
                if (ds.Tables[0].Rows[0]["OpenDeliveryNotesBalance"].ToString() != "")
                {
                    model.OpenDeliveryNotesBalance = decimal.Parse(ds.Tables[0].Rows[0]["OpenDeliveryNotesBalance"].ToString());
                }
                if (ds.Tables[0].Rows[0]["OpenOrdersBalance"].ToString() != "")
                {
                    model.OpenOrdersBalance = decimal.Parse(ds.Tables[0].Rows[0]["OpenOrdersBalance"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ECOrdersBalance"].ToString() != "")
                {
                    model.ECOrdersBalance = decimal.Parse(ds.Tables[0].Rows[0]["ECOrdersBalance"].ToString());
                }
                if (ds.Tables[0].Rows[0]["DiscountPercent"].ToString() != "")
                {
                    model.DiscountPercent = decimal.Parse(ds.Tables[0].Rows[0]["DiscountPercent"].ToString());
                }
                if (ds.Tables[0].Rows[0]["TaxRate"].ToString() != "")
                {
                    model.TaxRate = decimal.Parse(ds.Tables[0].Rows[0]["TaxRate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["TaxClassID"].ToString() != "")
                {
                    model.TaxClassID = int.Parse(ds.Tables[0].Rows[0]["TaxClassID"].ToString());
                }
                model.Email = ds.Tables[0].Rows[0]["Email"].ToString();
                if (ds.Tables[0].Rows[0]["LanguageID"].ToString() != "")
                {
                    model.LanguageID = int.Parse(ds.Tables[0].Rows[0]["LanguageID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["SalesPersonCode"].ToString() != "")
                {
                    model.SalesPersonCode = int.Parse(ds.Tables[0].Rows[0]["SalesPersonCode"].ToString());
                }
                model.ShippingType = ds.Tables[0].Rows[0]["ShippingType"].ToString();
                model.ShipToDefault = ds.Tables[0].Rows[0]["ShipToDefault"].ToString();
                if (ds.Tables[0].Rows[0]["Frozen"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["Frozen"].ToString() == "1") || (ds.Tables[0].Rows[0]["Frozen"].ToString().ToLower() == "true"))
                    {
                        model.Frozen = true;
                    }
                    else
                    {
                        model.Frozen = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["FrozenFrom"].ToString() != "")
                {
                    model.FrozenFrom = DateTime.Parse(ds.Tables[0].Rows[0]["FrozenFrom"].ToString());
                }
                model.FrozenRemarks = ds.Tables[0].Rows[0]["FrozenRemarks"].ToString();
                if (ds.Tables[0].Rows[0]["FrozenTo"].ToString() != "")
                {
                    model.FrozenTo = DateTime.Parse(ds.Tables[0].Rows[0]["FrozenTo"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Valid"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["Valid"].ToString() == "1") || (ds.Tables[0].Rows[0]["Valid"].ToString().ToLower() == "true"))
                    {
                        model.Valid = true;
                    }
                    else
                    {
                        model.Valid = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["ValidFrom"].ToString() != "")
                {
                    model.ValidFrom = DateTime.Parse(ds.Tables[0].Rows[0]["ValidFrom"].ToString());
                }
                model.ValidRemarks = ds.Tables[0].Rows[0]["ValidRemarks"].ToString();
                if (ds.Tables[0].Rows[0]["ValidTo"].ToString() != "")
                {
                    model.ValidTo = DateTime.Parse(ds.Tables[0].Rows[0]["ValidTo"].ToString());
                }
                model.Notes = ds.Tables[0].Rows[0]["Notes"].ToString();
                model.Description = ds.Tables[0].Rows[0]["Description"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select CustomerID,CardCode,CustomerType,CustomerForeignName,CustomerName,ContactPerson,Telephone,Mobile,Fax,Address,Country,Province,City,County,CreditLimit,Currency,CurrentAccountBalance,OpenDeliveryNotesBalance,OpenOrdersBalance,ECOrdersBalance,DiscountPercent,TaxRate,TaxClassID,Email,LanguageID,SalesPersonCode,ShippingType,ShipToDefault,Frozen,FrozenFrom,FrozenRemarks,FrozenTo,Valid,ValidFrom,ValidRemarks,ValidTo,Notes,Description ");
            strSql.Append(" FROM Customer ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetEnableCustomerNameAndID(string strWhere) 
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select CustomerID, CustomerForeignName,CustomerName, CardCode,    LanguageID");
            strSql.Append(" FROM Customer ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where Frozen=0 and Valid=1 and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
      /// <summary>            
        /// 分页获取数据列表  
      /// </summary>
      /// <param name="PageSize"></param>
      /// <param name="PageIndex"></param>
      /// <param name="CustomerName"></param>
      /// <param name="CardCode"></param>
      /// <param name="orderString">排序字段名 </param>
      /// <param name="orderType">设置排序类型, 非 0 值则降序 </param>
      /// <returns></returns>
        public DataSet GetList(int PageSize, int PageIndex, string CustomerName, string CardCode, string orderString, int orderType)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@OrderfldName", SqlDbType.VarChar,255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@CustomerName ", SqlDbType.VarChar,50),
                    new SqlParameter("@CardCode  ", SqlDbType.VarChar,50)
                    };
            parameters[0].Value = orderString;// "CustomerID";
            parameters[1].Value = PageSize;
            parameters[2].Value = PageIndex;
            parameters[3].Value = 1;
            parameters[4].Value = orderType;
            parameters[5].Value = CustomerName;
            parameters[6].Value = CardCode;
            return DbHelperSQL.RunProcedure("UP_Customer_GetPageList", parameters, "ds");
        }

        #endregion  成员方法
    }
}

